package com.charge.dao;

import com.charge.domain.Account;
import com.charge.domain.ColorModel;
import com.charge.domain.UserIncomeType;
import com.charge.domain.UserSpendingType;
import com.sun.org.apache.xpath.internal.WhitespaceStrippingElementMatcher;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;

/**
 * Description :
 * Author : 91658
 * Date : 2018/6/8 16:51
 **/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/applicationContext.xml")
public class ImportData {

    @Autowired
    private AccountMapping accountMapping;

    @Autowired
    private UserIncomeTypeMapping userIncomeTypeMapping;

    @Autowired
    private UserSpendingTypeMapping userSpendingTypeMapping;

    @Autowired
    private ColorMapping colorMapping;

    @Test
    public void test() throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("D:\\Java-Practice\\charge some\\account.xls"));
        HSSFSheet sheet = wb.getSheetAt(0);
        for (int i = 1;i< sheet.getPhysicalNumberOfRows(); i++) {
            Account account = new Account();
            HSSFRow row = sheet.getRow(i);
            account.setId(i);
            account.setUserId(1);
            account.setAccountName(row.getCell(2).getStringCellValue());
            account.setAccountBalance(new BigDecimal(row.getCell(3).getNumericCellValue()));
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            account.setCreateTime(new Date());
            accountMapping.insertSelective(account);
        }
    }

    @Test
    public void payments() throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("D:\\Java-Practice\\charge some\\fish.xls"));
        HSSFSheet sheet = wb.getSheetAt(0);
        Set<String> accountSet = new HashSet<>();
        Set<String> income = new HashSet<>();
        Set<String> spending = new HashSet<>();
        for (int i = 1;i< sheet.getPhysicalNumberOfRows(); i++) {
            HSSFRow row = sheet.getRow(i);
            String account = row.getCell(0).getStringCellValue();
            accountSet.add(account);

            String payments = row.getCell(1).getStringCellValue();
            String project = row.getCell(2).getStringCellValue();
            if ("支出".equals(payments)) {
                spending.add(project);
            }
            if ("收入".equals(payments)) {
                income.add(project);
            }
        }

        Iterator<String> accountIterator = accountSet.iterator();
        while (accountIterator.hasNext()){
            String accountName = accountIterator.next();
            Account account = new Account();
            account.setUserId(1);
            account.setAccountName(accountName);
            account.setCreateTime(new Date());
            accountMapping.insertSelective(account);
        }

        Iterator<String> incomeIterator = income.iterator();
        int incomeId = 1;
        while (incomeIterator.hasNext()) {
            UserIncomeType userIncomeType = new UserIncomeType();
            userIncomeType.setId(incomeId);
            userIncomeType.setUserId(1);
            userIncomeType.setColor("");
            userIncomeType.setIncomeName(incomeIterator.next());
            userIncomeType.setCreateTime(new Date());
            userIncomeTypeMapping.insertSelective(userIncomeType);
            incomeId++;
        }
        Iterator<String> spendingIterator = spending.iterator();
        int spendingId = 1;
        while (spendingIterator.hasNext()) {
            UserSpendingType userSpendingType = new UserSpendingType();
            userSpendingType.setId(spendingId);
            userSpendingType.setUserId(1);
            userSpendingType.setColor("");
            userSpendingType.setSpendingName(spendingIterator.next());
            userSpendingType.setCreateTime(new Date());
            userSpendingTypeMapping.insertSelective(userSpendingType);
            spendingId++;
        }

//        System.out.println(income);
//        System.out.println(spending);
    }

    @Test
    public void importColor() throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("D:\\Desktop\\color.xls"));
        HSSFSheet sheet = wb.getSheetAt(0);
        for (int i = 0;i< sheet.getPhysicalNumberOfRows(); i++) {
            ColorModel colorModel = new ColorModel();
            HSSFRow row = sheet.getRow(i);
            colorModel.setId((int) row.getCell(0).getNumericCellValue());
            colorModel.setEnglishCode(row.getCell(1).getStringCellValue());
            colorModel.setColorName(row.getCell(2).getStringCellValue());
            colorModel.setColorCode(row.getCell(3).getStringCellValue());
            // double numericCellValue = row.getCell(3).getNumericCellValue();
            int cellType = row.getCell(4).getCellType();
            if (cellType == 0) {
                colorModel.setRgb(row.getCell(4).getNumericCellValue() + "");
            } else if (cellType == 1){
                colorModel.setRgb(row.getCell(4).getStringCellValue());
            }
            colorModel.setCreateTime(new Date());
            System.out.println("下标======" + i);
            colorMapping.insertSelective(colorModel);
        }
    }
}
